SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TDCANJ_CLIE_Q02]  
/*----------------------------------------------------------------*/  
/*-- Empresa  : OFISIS S.A.      */  
/*-- Cliente  : MANUELA RAMOS      */  
/*-- Sistema  : Empresa      */  
/*-- Programa  : Reporte Seguimiento Documentos    */  
/*-- Script  : TDCANJ_CLIE_Q02       */  
/*-- Nombre  : SP_TDCANJ_CLIE_Q02         */  
/*-- Desarrollado : Eduardo Vivar Pomiano       */  
/*-- Fecha  : 08/02/2000       */  
/*-- Base de Datos : MS SQL Server      */  
/*-- Versi½n  : 7.0       */  
/*----------------------------------------------------------------*/  
/*----------------------------------------------------------------*/  
/*-- Modificado por  :        */  
/*-- Fecha modificaci½n  :        */  
/*-- Detalle modificaci½n:        */  
/*----------------------------------------------------------------*/  
/*----------------------------------------------------------------*/  
/* drop proc SP_TDCANJ_CLIE_Q02        */  
/* grant all on SP_TDCANJ_CLIE_Q02 to public     */  
/* SP_TDCANJ_CLIE_Q02 '99', '10009243', '00000001','00000001'    */  
/*----------------------------------------------------------------*/  
( @ISCO_EMPR TD_VC_002,  
  @ISCO_CLIE TD_VC_020,  
  @ISNU_CANJ TD_VC_008,  
  @ISNU_RENO TD_VC_008  
)  
 As  
Declare  
 @VSTI_DOCU        TD_VC_003,  
 @VSNU_DOCU_CLIE   TD_VC_020,  
 @VSNU_LETR_CLIE   TD_VC_020,   
 @VSNU_SECU        TD_VC_001,  
 @VSNU_SECU_AUXI   TD_IN_001,  
 @OSTI_DOCU    TD_VC_003     
  
Create Table #TWCANJ_CLIE  
   ( NU_OPER Varchar(9) NULL,   
    NU_SECU      Smallint  NULL,    
    CO_TIPO_DOCU   Varchar(3)   NULL,  
    NU_DOCU_CLIE   Varchar(20)  NULL,   
    TI_DOCU_LETR   Varchar(3)   NULL,  
    NU_LETR_CLIE   Varchar(15)  NULL)   
  
Select @VSNU_SECU = 0  
  
EXEC SP_TTDOCU_CNTB_Q02 6, @OSTI_DOCU OUTPUT  
  
-- TODOS LOS DOCUMENTOS DEL CANJE  
Declare CU_TDCANJ_CLIE_Q02_1 cursor  
For  
Select CO_TIPO_DOCU, NU_DOCU_CLIE  
From TDCANJ_CLIE  
Where CO_EMPR = @ISCO_EMPR   
And CO_CLIE = @ISCO_CLIE   
And NU_CANJ = @ISNU_CANJ  
And CO_TIPO_DOCU IS NOT NULL   
  
Open CU_TDCANJ_CLIE_Q02_1  
Fetch CU_TDCANJ_CLIE_Q02_1 into @VSTI_DOCU, @VSNU_DOCU_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
         Raiserror 20001 "Fin de archivo"  
         Close CU_TDCANJ_CLIE_Q02_1  
         Deallocate CU_TDCANJ_CLIE_Q02_1  
         Return   
       end   
  
    Select @VSNU_SECU = @VSNU_SECU + 1  
  
    Insert into #TWCANJ_CLIE (NU_OPER, NU_SECU, CO_TIPO_DOCU, NU_DOCU_CLIE)      
    Values ('C'+@ISNU_CANJ, @VSNU_SECU, @VSTI_DOCU, @VSNU_DOCU_CLIE)  
  
    Fetch CU_TDCANJ_CLIE_Q02_1 into @VSTI_DOCU, @VSNU_DOCU_CLIE  
  End  
  
Close CU_TDCANJ_CLIE_Q02_1  
Deallocate CU_TDCANJ_CLIE_Q02_1  
  
Select @VSNU_SECU = 0  
  
-- TODAS LAS LETRAS ORIGINADAS POR ESE CANJE  
Declare CU_TDCANJ_CLIE_Q02_1 cursor  
For  
Select @OSTI_DOCU, T1.NU_LETR_CLIE  
From TDCANJ_CLIE T1, TMLETR_CLIE T2  
Where T1.CO_EMPR = @ISCO_EMPR and  
T1.CO_EMPR = T2.CO_EMPR and  
T1.CO_CLIE = @ISCO_CLIE and  
T1.CO_CLIE = T2.CO_CLIE  and  
T1.NU_LETR_CLIE = T2.NU_LETR_CLIE  and  
T1.NU_CANJ = @ISNU_CANJ AND  
T1.NU_LETR_CLIE IS NOT NULL   
  
Open CU_TDCANJ_CLIE_Q02_1  
Fetch CU_TDCANJ_CLIE_Q02_1 into @VSTI_DOCU, @VSNU_LETR_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
         Raiserror 20001 "Fin de archivo"  
         Close CU_TDCANJ_CLIE_Q02_1  
         Deallocate CU_TDCANJ_CLIE_Q02_1  
         Return   
       end   
  
    Select @VSNU_SECU = @VSNU_SECU + 1  
  
    If NOT Exists ( Select * From #TWCANJ_CLIE Where NU_SECU = @VSNU_SECU)  
       Insert into #TWCANJ_CLIE (NU_OPER, NU_SECU, TI_DOCU_LETR, NU_LETR_CLIE)      
       Values ('C'+@ISNU_CANJ, @VSNU_SECU, @VSTI_DOCU, @VSNU_LETR_CLIE)  
    Else  
       Update #TWCANJ_CLIE   
       Set TI_DOCU_LETR = @VSTI_DOCU,       NU_LETR_CLIE = @VSNU_LETR_CLIE  
       Where NU_SECU = @VSNU_SECU    
  
    Fetch CU_TDCANJ_CLIE_Q02_1 into @VSTI_DOCU, @VSNU_LETR_CLIE  
  End  
  
close CU_TDCANJ_CLIE_Q02_1  
deallocate CU_TDCANJ_CLIE_Q02_1  
  
  
-- TODOS LOS DOCUMENTOS DE LA RENOVACION  
Select @VSNU_SECU_AUXI = @VSNU_SECU  
  
Declare CU_SP_TDRENO_CLIE_Q01 cursor  
For  
Select CO_TIPO_DOCU, NU_DOCU_CLIE  
From TDRENO_CLIE  
Where CO_EMPR = @ISCO_EMPR   
And CO_CLIE = @ISCO_CLIE   
And NU_RENO = @ISNU_RENO   
And CO_TIPO_DOCU IS NOT NULL   
  
Open CU_SP_TDRENO_CLIE_Q01  
Fetch CU_SP_TDRENO_CLIE_Q01 into @VSTI_DOCU, @VSNU_DOCU_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
 Raiserror 20001 "Fin de archivo"  
         Close CU_SP_TDRENO_CLIE_Q01  
         Deallocate CU_SP_TDRENO_CLIE_Q01  
         Return   
       end   
  
    Select @VSNU_SECU = @VSNU_SECU + 1  
  
    Insert into #TWCANJ_CLIE (NU_OPER, NU_SECU, CO_TIPO_DOCU, NU_DOCU_CLIE)      
    Values ('R'+@ISNU_RENO, @VSNU_SECU, @VSTI_DOCU, @VSNU_DOCU_CLIE)  
  
    Fetch CU_SP_TDRENO_CLIE_Q01 into @VSTI_DOCU, @VSNU_DOCU_CLIE  
  End  
  
Close CU_SP_TDRENO_CLIE_Q01  
deallocate CU_SP_TDRENO_CLIE_Q01  
  
--LETRAS ORIGINADAS POR LA RENOVACION  
Declare CU_SP_TDRENO_CLIE_Q01 cursor  
For  
Select @OSTI_DOCU, T1.NU_LETR_CLIE  
From TDRENO_CLIE T1, TMLETR_CLIE T2  
Where T1.CO_EMPR = @ISCO_EMPR   
And T1.CO_EMPR = T2.CO_EMPR   
And T1.CO_CLIE= @ISCO_CLIE   
And T1.CO_CLIE = T2.CO_CLIE    
And T1.NU_LETR_CLIE = T2.NU_LETR_CLIE    
And T1.NU_RENO = @ISNU_RENO   
And T1.NU_LETR_CLIE IS NOT NULL   
  
Open CU_SP_TDRENO_CLIE_Q01  
Fetch CU_SP_TDRENO_CLIE_Q01 into @VSTI_DOCU, @VSNU_LETR_CLIE  
While @@fetch_status = 0  
  Begin  
    If @@fetch_status != 0  
       Begin  
         Raiserror 20001 "Fin de archivo"  
         Close CU_SP_TDRENO_CLIE_Q01  
         Deallocate CU_SP_TDRENO_CLIE_Q01  
         Return   
       end   
  
    Select @VSNU_SECU_AUXI = @VSNU_SECU_AUXI + 1  
  
    If NOT Exists ( Select * From #TWCANJ_CLIE Where NU_SECU = @VSNU_SECU_AUXI)  
       Insert into #TWCANJ_CLIE (NU_OPER, NU_SECU, TI_DOCU_LETR, NU_LETR_CLIE)      
       Values ('R'+@ISNU_RENO, @VSNU_SECU_AUXI, @VSTI_DOCU, @VSNU_LETR_CLIE)  
    Else  
       Update #TWCANJ_CLIE   
       Set TI_DOCU_LETR = @VSTI_DOCU,   
    NU_LETR_CLIE = @VSNU_LETR_CLIE  
       Where NU_SECU = @VSNU_SECU_AUXI    
  
    Fetch CU_SP_TDRENO_CLIE_Q01 Into @VSTI_DOCU, @VSNU_LETR_CLIE  
  End  
  
Close CU_SP_TDRENO_CLIE_Q01  
Deallocate CU_SP_TDRENO_CLIE_Q01  
  
Select NU_OPER, CO_TIPO_DOCU, NU_DOCU_CLIE, TI_DOCU_LETR, NU_LETR_CLIE   
From #TWCANJ_CLIE  
ORDER BY NU_OPER, NU_SECU  
  
Return  
  
/*------------------------------ FIN ---------------------------------*/


GO
